package org.vacoor.nothing.groovy

import com.google.common.base.Stopwatch
import groovy.sql.Sql
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.test.context.ContextConfiguration
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner

import java.sql.ResultSet
import java.sql.SQLException
import java.util.concurrent.TimeUnit

/**
 * Created by Administrator on 2014/12/4.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext-groovy.xml")
class DemoTest2 extends AbstractJUnit4SpringContextTests {
    @Autowired
    Demo demo;

    @Test
    void testPrint() {
        println "-------------+"
        demo.print();
        println "-------------"
    }

    @Test
    void testSql() {
        def url = "jdbc:oracle:thin:@192.168.1.123:1521:orcl",
            url2 = "jdbc:p6spy:oracle:thin:@121.41.31.94:1521:orcl",
            username = "jiusu",
            password = "jiusu",
            srcDb = Sql.newInstance(url, username, password),
            targetDb = Sql.newInstance(url2, username, password);
        def stopwatch = com.google.common.base.Stopwatch.createStarted()

        srcDb.call("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE)}");
        srcDb.call("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE)}");
        srcDb.call("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE)}");
        srcDb.call("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',FALSE)}");
        srcDb.call("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SIZE_BYTE_KEYWORD',FALSE)}");
        srcDb.call("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE)}");

        // 按照日期排序是为了方便表依赖处理
        srcDb.eachRow("SELECT OBJECT_NAME TABLE_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' ORDER BY CREATED", { tab ->
            def tabName = tab.TABLE_NAME, t;

            // 擦，表结构会变
            /*
            try {
                targetDb.execute("DROP TABLE " + tabName + " CASCADE CONSTRAINTS");
            } catch (SQLException ignore) {}
            */

            t = targetDb.firstRow("SELECT * FROM USER_TABLES WHERE TABLE_NAME = ${tabName}");

            // 如果表不存在
            if (!t) {
                println "not exists table: ${tabName}";
                def ddl = srcDb.firstRow("SELECT DBMS_METADATA.GET_DDL('TABLE', ${tabName}) TEXT FROM DUAL");
                // 替换后特殊标示肿么办
                ddl = ddl.TEXT.getSubString(1, (int) ddl.TEXT.length())/*.replace('"', '')*/.replaceAll(/(?<= )[^. ,]+\.(?=[A-Za-z])/, '');

                println "created table: ${tabName}, ddl: ${ddl}";
                targetDb.execute(ddl);
            }

            targetDb.execute("TRUNCATE TABLE " + tabName);
            srcDb.rows("SELECT * FROM " + tabName).each({ row ->
                def sql = "INSERT INTO " + tabName + " (",
                    vals = " VALUES (";
                row.keySet().each({ k ->
                    sql += k + ", ";
                    vals += ":" + k + ", ";
                });
                sql = sql.replaceAll(/, $/, ')');
                sql += vals.replaceAll(/, $/, ')');

                // INSERT 会自动生成 id see: groovy.sql.Sql.executeInsert(java.lang.String, java.util.List<java.lang.Object>)
                // targetDb.executeInsert(row, sql);
                println "insert ${tabName} values ${row}";
                targetDb.executeUpdate(row, sql);
            });
        });
        // 还原设置
        srcDb.call("{call DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT')}");

        println stopwatch.stop().elapsed(TimeUnit.SECONDS) + "s";
    }
}
